﻿CREATE PROCEDURE dbo.s_MenusCount
AS
DECLARE @LoanIDs TABLE(LoanID int)
--INSERT INTO @LoanIDs
--SELECT * FROM dbo.fs_Loans_Read(DEFAULT, DEFAULT)
--UNION ALL
--SELECT * FROM dbo.fs_Verify_Read(DEFAULT, DEFAULT)

DECLARE @MenuIDs TABLE(MenuID int,ParentMenuID int,WorkFlowIndex int,MenuName varchar(128))
INSERT INTO @MenuIDs
SELECT ID MenuID,ParentID ParentMenuID,StateFlowPos,Name MenuName FROM v_State

SELECT * FROM
(
SELECT TOP 100 PERCENT ISNULL(t_Menu_1.MenuName, 'TOTAL') AS ParentName, ISNULL(ISNULL(t_Menu.MenuName, t_Menu_1.MenuName), 'TOTAL') 
                      + ' - ' + CONVERT(varchar, MENUS.[Count]) AS Text, MENUS.[Count], ISNULL(MENUS.StatusID, MENUS.ParentMenuID) AS MenuID, 
                      ISNULL(t_Menu.ParentMenuID, 0) AS ParentMenuID, 'true' AS EXPANDED
FROM         (SELECT     CASE WHEN (GROUPING(ParentMenuID) = 1) THEN - 1 ELSE ISNULL(CONVERT(varchar, ParentMenuID), 'UNKNOWN') 
                                              END AS ParentMenuID, t_Loan.StatusID, COUNT(*) AS [Count]
                       FROM          t_Loan INNER JOIN
                                              @MenuIDs t_Menu ON t_Loan.StatusID = t_Menu.MenuID
INNER JOIN (SELECT * FROM dbo.fs_Loans_Read(DEFAULT, DEFAULT)) fs_Loans_Read ON t_Loan.ID = fs_Loans_Read.LoanID
                       GROUP BY t_Menu.ParentMenuID, t_Loan.StatusID WITH rollup) MENUS LEFT OUTER JOIN
                      @MenuIDs t_Menu_1 ON MENUS.ParentMenuID = t_Menu_1.MenuID LEFT OUTER JOIN
                      @MenuIDs t_Menu ON MENUS.StatusID = t_Menu.MenuID
ORDER BY t_Menu_1.WorkFlowIndex, t_Menu.WorkFlowIndex
)T
UNION ALL
SELECT * FROM
(
SELECT TOP 100 PERCENT ISNULL(t_Menu_1.MenuName, 'TOTAL in Verification') AS ParentName, ISNULL(ISNULL(t_Menu.MenuName, t_Menu_1.MenuName), 'TOTAL') 
                      + ' - ' + CONVERT(varchar, MENUS.[Count]) AS Text, MENUS.[Count], ISNULL(MENUS.StatusID, MENUS.ParentMenuID) AS MenuID, 
                      ISNULL(t_Menu.ParentMenuID, 0) AS ParentMenuID, 'true' AS EXPANDED
FROM         (SELECT     CASE WHEN (GROUPING(ParentMenuID) = 1) THEN - 2 ELSE ISNULL(CONVERT(varchar, ParentMenuID), 'UNKNOWN') 
                                              END AS ParentMenuID, t_Loan.StatusID, COUNT(*) AS [Count]
                       FROM         t_VerificationSmart t_Loan INNER JOIN
                                              @MenuIDs t_Menu ON t_Loan.StatusID = t_Menu.MenuID INNER JOIN
(SELECT * FROM dbo.fs_Verify_Read(DEFAULT, DEFAULT)) fs_Loans_Read ON t_Loan.LoanID = fs_Loans_Read.LoanID
                       GROUP BY t_Menu.ParentMenuID, t_Loan.StatusID WITH rollup) MENUS LEFT OUTER JOIN
                      @MenuIDs t_Menu_1 ON MENUS.ParentMenuID = t_Menu_1.MenuID LEFT OUTER JOIN
                      @MenuIDs t_Menu ON MENUS.StatusID = t_Menu.MenuID
ORDER BY t_Menu_1.WorkFlowIndex, t_Menu.WorkFlowIndex
)V
UNION ALL
SELECT * FROM
(
SELECT TOP 100 PERCENT ISNULL(t_Menu_1.MenuName, 'TOTAL in Purchase') AS ParentName, ISNULL(ISNULL(t_Menu.MenuName, t_Menu_1.MenuName), 'TOTAL') 
                      + ' - ' + CONVERT(varchar, MENUS.[Count]) AS Text, MENUS.[Count], ISNULL(MENUS.StatusID, MENUS.ParentMenuID) AS MenuID, 
                      ISNULL(t_Menu.ParentMenuID, 0) AS ParentMenuID, 'true' AS EXPANDED
FROM         (SELECT     CASE WHEN (GROUPING(ParentMenuID) = 1) THEN - 3 ELSE ISNULL(CONVERT(varchar, ParentMenuID), 'UNKNOWN') 
                                              END AS ParentMenuID, PB.StatusID, COUNT(*) AS [Count]
                       FROM         t_PurchaseBatch PB INNER JOIN
                                              @MenuIDs t_Menu ON PB.StatusID = t_Menu.MenuID INNER JOIN
(SELECT * FROM dbo.fs_Batch_Read(DEFAULT, DEFAULT)) fs_Batch_Read ON PB.ID = fs_Batch_Read.BatchID
                       GROUP BY t_Menu.ParentMenuID, PB.StatusID WITH rollup) MENUS LEFT OUTER JOIN
                      @MenuIDs t_Menu_1 ON MENUS.ParentMenuID = t_Menu_1.MenuID LEFT OUTER JOIN
                      @MenuIDs t_Menu ON MENUS.StatusID = t_Menu.MenuID
ORDER BY t_Menu_1.WorkFlowIndex, t_Menu.WorkFlowIndex
)V

